#include <stdio.h>
#include <sys/socket.h>
#include <stdlib.h>
#include <netinet/in.h>
#include <string.h>
#include <pthread.h>
#include <arpa/inet.h>
#include <sys/types.h>
#include <sqlite3.h>
#include <unistd.h>
#include <time.h>

#define ERRLOG(errmsg) do{\
	perror(errmsg);\
	printf("%s:%d\n",__func__,__LINE__);\
    }while(0)
//exit(0); 出现错误信息就结束线程
#define textlen 128
#define SQname "user1.db"
typedef struct{
    int acceptfd;
    struct sockaddr_in clientaddr;
}client_msg;
char user_information[][16] = {"name","passwd","mod","id",\
                            "age","sex","wages","department"};

typedef struct{
    char type;//1注册 2登陆 3增加 4删除 5修改 6查找
    char text[textlen];
}msg_t;
msg_t msg;
int msglen = sizeof(msg_t);

//  数据库句柄
sqlite3 *db;
time_t mysec;
struct tm *mytime;

//注册
int do_register(int);
//登录
int do_login(int);
//增加 
int do_add(int);
//查找
int do_find(int);
//删除
int do_delete(int);
//修改
int do_modify(int);
//历史
int do_history(int);
int record(char *,const char *);

void *pthread_func(void *arg)
//参数：client_msg
{
    //分离态
    pthread_detach(pthread_self());	
    //printf("create pthread :%ld\n",pthread_self());
    ssize_t bytes;
    client_msg cmsg = *(client_msg *)arg;
    int acceptfd = cmsg.acceptfd;

    //printf("acceptfd = %d\n",acceptfd);
    while(1)
    {
        if((bytes = recv(acceptfd, &msg, msglen, 0)) == -1)
		{
			ERRLOG("recv error");
		}
		else if(bytes == 0)
		{
			printf("%s:%d close..\n",inet_ntoa(cmsg.clientaddr.sin_addr),ntohs(cmsg.clientaddr.sin_port));
			close(acceptfd);
            pthread_exit(NULL);
		}
		printf("[%s:%d]:%d-%s\n",inet_ntoa(cmsg.clientaddr.sin_addr),ntohs(cmsg.clientaddr.sin_port),msg.type,msg.text);
	    
        switch(msg.type)
        {
        case 1:do_register(acceptfd);break;
        case 2:do_login(acceptfd);break;
        case 3:do_add(acceptfd);break;
        case 4:do_delete(acceptfd);break;
        case 5:do_modify(acceptfd);break;
        case 6:do_find(acceptfd);break;
        case 7:do_history(acceptfd);break;
        }
    }

}
int main (int argc, char const *argv[])
{
	if(argc < 3)
	{
		fprintf(stderr,"Usage: %s <ip> <port>\n",argv[0]);
		exit(1);
	}
	int sockfd,acceptfd;
	struct sockaddr_in serveraddr;
	socklen_t addrlen = sizeof(struct sockaddr_in);
	struct sockaddr_in clientaddr;
	//创建套接字
	if((sockfd = socket(AF_INET, SOCK_STREAM, 0)) == -1)
	{
		ERRLOG("socket error");
	}	
	//填充网络信息结构体
	serveraddr.sin_family = AF_INET;
	serveraddr.sin_addr.s_addr = inet_addr(argv[1]);
	serveraddr.sin_port = htons(atoi(argv[2]));
	
	//将套接字与服务器网络信息结构体绑定
	if(bind(sockfd, (struct sockaddr *)&serveraddr ,addrlen) == -1)
	{
		ERRLOG("bind error");
	}
	//将套接字设置为被动监听状态
	if(listen(sockfd ,5) == -1)
	{
		ERRLOG("listen error");
	}

 //数据库
    if(sqlite3_open(SQname,&db) != SQLITE_OK)
    {
        ERRLOG("sqlite3 open error");
    }
    else 
    {
        printf("数据库%s打开成功..\n",SQname);
    }
    char *errmsg;
   //创表
    if(sqlite3_exec(db,"create table if not exists  user(name char,passwd char,mod int,"
                        "id int,age int,sex char,wages int,department char)"
                    ,NULL,NULL,&errmsg) != SQLITE_OK)
    {
        printf("%s\n",errmsg);
    }
    else
    {
        printf("create table user success..\n");
    }
    if(sqlite3_exec(db,"create table if not exists history (time char,user char,action char)",NULL,NULL,&errmsg) != SQLITE_OK)
    {
        printf("%s\n",errmsg);
    }
    else
    {
        printf("历史记录表history创建成功.\n");
    }
    pthread_t thread;
    client_msg cmsg;
    while(1)
    {
	    //阻塞等待客户端连接
	    if((acceptfd = accept(sockfd, (struct sockaddr *)&clientaddr, &addrlen)) == -1)
	    {
		    ERRLOG("accept error");
	    }
	    printf("[%s:%d] connect !\n",inet_ntoa(clientaddr.sin_addr),ntohs(clientaddr.sin_port));
        cmsg.acceptfd = acceptfd;
        cmsg.clientaddr = clientaddr;
        
        //创建子线程
        if(pthread_create(&thread,NULL,pthread_func,&cmsg) != 0)
        {
            ERRLOG("pthread create error");
        }
    }
}
//--------------------注册----------------------------
int do_register(int acceptfd)
{
    char *name = strtok(msg.text,":");
    char *passwd = strtok(NULL,":");
    char buf[128] = {0};
    char *errmsg = NULL;
    //获取用户信息->重名判断
    sprintf(buf,"select * from user where name='%s'",name);
    char **getdata;
    int pnrow;
    int pncolumn;
    //创建一个标签
    if(sqlite3_get_table(db,buf,&getdata,&pnrow,&pncolumn,&errmsg) != SQLITE_OK)
    {
        printf("%s\n",errmsg);
    }
    //printf("pnrow = %d\n",pnrow);
    if(pnrow >= 1)//有重名
    {
        msg.type = -1;
        memset(msg.text,0,textlen);
        sprintf(msg.text,"user name exist");
        if(send(acceptfd, &msg, msglen, 0) == -1)
        {
            ERRLOG("send error");
        }
    }
    else//无重名
    {
        //检测、分配工号
        int id = 0;
        memset(buf,0,sizeof(buf));
        sprintf(buf,"select id from user order by id desc limit 1");
        if(sqlite3_get_table(db,buf,&getdata,&pnrow,&pncolumn,&errmsg) != SQLITE_OK)
        {
            printf("%s\n",errmsg);
        }
        if(pnrow == 0)
        {
            id = 200001;
            printf("distribute id:%d\n",id);
        }
        else
        {
            id = atoi(*(getdata+1))+1;
            printf("distribute id:%d\n",id);
        }
        memset(buf,0,sizeof(buf));
        sprintf(buf,"insert into user (name,passwd,mod,id) values ('%s','%s',1,'%d')",name,passwd,id);
    
        if(sqlite3_exec(db,buf,NULL,NULL,&errmsg) != SQLITE_OK)
        {
            printf("%s\n",errmsg);
        }
        else
        {
            printf("insert user %s message success\n",name);
        }
        msg.type = 0;
        memset(msg.text,0,textlen);
        sprintf(msg.text,"register successed");
        if(send(acceptfd, &msg, msglen, 0) == -1)
	    {
    	    ERRLOG("send error");
	    }
        record(name,"register");
    }
    return 1;
}
//--------------------登录----------------------------
int do_login(int acceptfd)
{
    char name[32] = {0};
    strcpy(name,strtok(msg.text,":"));
    char *passwd = strtok(NULL,":");
    char buf[128] = {0};
    char *errmsg = NULL;
    //获取用户信息->密码判断
    sprintf(buf,"select name,passwd,mod,id from user where name='%s' and passwd='%s'",name,passwd);
    char **getdata;
    int pnrow;
    int pncolumn;
    if(sqlite3_get_table(db,buf,&getdata,&pnrow,&pncolumn,&errmsg) != SQLITE_OK)
    {
        printf("%s\n",errmsg);
    }
    printf("pnrow = %d\n",pnrow);
    memset(msg.text,0,textlen);
    if(pnrow >= 1)
    {
        memset(buf,0,sizeof(buf));

        msg.type = 0;
        sprintf(msg.text,"%c %d login success",(**(getdata+6)),atoi(*(getdata+7)));
        printf("userid:%d\n",atoi(*(getdata+7)));
        printf("usermod:%c\n",**(getdata+6));//4个字段名name passwd mod id
        if(send(acceptfd, &msg, msglen, 0) == -1)
        {
            ERRLOG("send error");
        }
        record(name,"login");
    }
    else
    {
        msg.type = -1;
        sprintf(msg.text,"user information error");
        if(send(acceptfd, &msg, msglen, 0) == -1)
        {
            ERRLOG("send error");
        }
    }
    return 1;
}

//--------------------------添加---------------------------------
int do_add(int acceptfd)
{
    char name[32] = {0};
    strcpy(name,strtok(msg.text,":"));
    char *user_name = strtok(NULL," ");
    char *passwd = strtok(NULL," ");
    char *mod = strtok(NULL," ");
    char *id = strtok(NULL," ");
    char *age = strtok(NULL," ");
    char *sex = strtok(NULL," ");
    char *wage = strtok(NULL," ");
    char *department = strtok(NULL," ");
    char buf[128] = {0};
    char *errmsg = NULL;
    //修改用户信息->根据id
    sprintf(buf,"insert into user values('%s','%s','%s','%s','%s','%s','%s','%s')"\
                                    ,user_name,passwd,mod,id,age,sex,wage,department);
    if(sqlite3_exec(db,buf,NULL,NULL,&errmsg) != SQLITE_OK)
    {
        printf("%s\n",errmsg);
        msg.type = -1;
        memset(msg.text,0,textlen);
        sprintf(msg.text,"add failed");
        if(send(acceptfd, &msg, msglen, 0) == -1)
        {
            ERRLOG("send error");
        }
        return 0;
    }
    else//成功
    {
        msg.type = 0;
        memset(msg.text,0,textlen);
        sprintf(msg.text,"add successed");
        if(send(acceptfd, &msg, msglen, 0) == -1)
        {
            ERRLOG("send error");
        }
        record(name,buf);
        return 1;
    }
}
//-------------------------------------删除----------------------------------
int do_delete(int acceptfd)
{
    char name[32] = {0};
    strcpy(name,strtok(msg.text,":"));
    int id = atoi(strtok(NULL,":"));
    char buf[128] = {0};
    char *errmsg = NULL;
    //删除用户->根据id
    sprintf(buf,"delete from user where id='%d'",id);
    if(sqlite3_exec(db,buf,NULL,NULL,&errmsg) != SQLITE_OK)
    {
        printf("%s\n",errmsg);
        msg.type = -1;
        memset(msg.text,0,textlen);
        sprintf(msg.text,"delete failed");
        if(send(acceptfd, &msg, msglen, 0) == -1)
        {
            ERRLOG("send error");
        }
        return 0;
    }
    else//成功
    {
        msg.type = 0;
        memset(msg.text,0,textlen);
        sprintf(msg.text,"delete successed");
        if(send(acceptfd, &msg, msglen, 0) == -1)
        {
            ERRLOG("send error");
        }
        record(name,buf);
        return 1;
    }

}
//------------------------------------记录-----------------------------
int record(char *name,const char *action)
{
    char h_time[32] = {0};
    char buf[128] = {0};
    char *errmsg;
    time(&mysec);
    mytime = localtime(&mysec);
    sprintf(h_time,"%d-%02d-%02d %02d:%02d:%02d"
            ,mytime->tm_year+1900
            ,mytime->tm_mon+1
            ,mytime->tm_mday
            ,mytime->tm_hour
            ,mytime->tm_min
            ,mytime->tm_sec);
    sprintf(buf,"insert into history values ('%s','%s',\"%s\")",h_time,name,action); 
    
    if(sqlite3_exec(db,buf,NULL,NULL,&errmsg) != SQLITE_OK)
    {
        printf("record failed\n");
        printf("%s\n",errmsg);
        return 0;
    }
    else//成功
    {
        printf("record successed\n");
        return 1;
    }
}
//----------------------------------------查找---------------------------------------
int do_find(int acceptfd)
{
    char name[32] = {0};
    strcpy(name,strtok(msg.text,":"));
    int id = atoi(strtok(NULL,":"));
    char buf[128] = {0};
    char *errmsg = NULL;
    //获取用户信息->根据id 0全部查找
    if(id == 0)
    {
        sprintf(buf,"select * from user");
    }
    else
    {
        sprintf(buf,"select * from user where id='%d'",id);
    }
    char **getdata;
    int pnrow;
    int pncolumn;
    if(sqlite3_get_table(db,buf,&getdata,&pnrow,&pncolumn,&errmsg) != SQLITE_OK)
    {
        printf("%s\n",errmsg);
    }
    //循环发出
        int i;
        //printf("pncolumn = %d",pncolumn);
        for(i = 0;i < pnrow;i++)
        {
            msg.type = 0;
            memset(msg.text,0,textlen);
            getdata += pncolumn;
            sprintf(msg.text,"%s %s %s %s %s %s %s %s"\
                    ,*(getdata+0),*(getdata+1),*(getdata+2),*(getdata+3)\
                    ,*(getdata+4),*(getdata+5),*(getdata+6),*(getdata+7));
            if(send(acceptfd, &msg, msglen, 0) == -1)
            {
                ERRLOG("send error");
            }
        }
    //终止信号
        msg.type = -1;
        memset(msg.text,0,textlen);
        sprintf(msg.text,"find end");
        if(send(acceptfd, &msg, msglen, 0) == -1)
        {
            ERRLOG("send error");
        }
        record(name,buf);
        return 1;
}
//-----------------------------------修改-------------------------------
int do_modify(int acceptfd)
{
    char name[32] = {0};
    strcpy(name,strtok(msg.text,":"));
    int id = atoi(strtok(NULL,":"));
    char *type = strtok(NULL,":");
    char *data = strtok(NULL,":");
    char buf[128] = {0};
    char *errmsg = NULL;
    //修改用户信息->根据id
    sprintf(buf,"update user set %s='%s' where id='%d'",type,data,id);
    if(sqlite3_exec(db,buf,NULL,NULL,&errmsg) != SQLITE_OK)
    {
        printf("%s\n",errmsg);
        msg.type = -1;
        memset(msg.text,0,textlen);
        sprintf(msg.text,"modify failed");
        if(send(acceptfd, &msg, msglen, 0) == -1)
        {
            ERRLOG("send error");
        }
        return 0;
    }
    else//成功
    {
        msg.type = 0;
        memset(msg.text,0,textlen);
        sprintf(msg.text,"modify successed");
        if(send(acceptfd, &msg, msglen, 0) == -1)
        {
            ERRLOG("send error");
        }
        record(name,buf);
        return 1;
    }
}

//-----------------------------------历史-------------------------
int do_history(int acceptfd)
{
    char name[32] = {0};
    strcpy(name,strtok(msg.text,":"));
    char *dest_name = strtok(NULL,":");
    char *num = strtok(NULL,":");
    char buf[128] = {0};
    char *errmsg = NULL;
    //获取历史记录->根据名字 0全部查找
    if(strcmp(dest_name,"0") == 0)
    {
        sprintf(buf,"select * from history order by time desc");
    }
    else
    {
        sprintf(buf,"select * from history where user='%s' order by time desc",dest_name);
    }
    if(strcmp(num,"0") != 0)
    {
        sprintf(buf+strlen(buf)," limit %s",num);
    }
    char **getdata;
    int pnrow;
    int pncolumn;
    if(sqlite3_get_table(db,buf,&getdata,&pnrow,&pncolumn,&errmsg) != SQLITE_OK)
    {
        printf("%s\n",errmsg);
    }
    //循环发出
        int i;
        //printf("pncolumn = %d",pncolumn);
        for(i = 0;i < pnrow;i++)
        {
            msg.type = 0;
            memset(msg.text,0,textlen);
            getdata += pncolumn;
            sprintf(msg.text,"%s//%s//%s"\
                    ,*(getdata+0),*(getdata+1),*(getdata+2));
            if(send(acceptfd, &msg, msglen, 0) == -1)
            {
                ERRLOG("send error");
            }
        }
    //终止信号
        msg.type = -1;
        memset(msg.text,0,textlen);
        sprintf(msg.text,"find end");
        if(send(acceptfd, &msg, msglen, 0) == -1)
        {
            ERRLOG("send error");
        }
        record(name,buf);
        return 1;

}


